﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Runda.EShop.Common.Utility;
using System.Text;

namespace Runda.EShop.Dal
{
    /// <summary>
    /// DAL layer class dbo.Sys_Notice.
    /// </summary>
    public partial class SysNoticeDal : DalBase
    {
        public DataTable GetNoticeListNonContent(List<SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"SELECT {0} NoticeID,[Title],CreatedAt,CreatedName FROM Sys_Notice WHERE Deleted = 0 {1}");
            string sqlStr = SqlDbParameter.BuildSqlString(sqlSb.ToString(), parms);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
    }
}
